package hutech.ksv.dao;

import hutech.ksv.common.KenhSinhVienContants;
import hutech.ksv.entity.ChiTietPNXNTEntity;
import hutech.ksv.entity.ChiTietPNXQAEntity;
import hutech.ksv.entity.PhieuNhanXetNTEntity;
import hutech.ksv.entity.PhieuNhanXetQAEntity;
import hutech.ksv.entity.QuanAnEntity;
import hutech.ksv.entity.ThongTinNhanXetEntity;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class NhanXetDao implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	@Autowired
	DataSource dataSource;
	
	public int themPhieuNhanXetNT(PhieuNhanXetNTEntity phieuNhanXetNT) {
		String query = "INSERT INTO `phieunhanxetnt` (`DanhGiaNT`,`TenTKSV`) "
				+ "VALUES (?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setInt(1, phieuNhanXetNT.getDanhGiaNT());
			preparedStatement.setString(2, phieuNhanXetNT.getTenTKSV());
			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}
	
	public int themPhieuNhanXetQA(PhieuNhanXetQAEntity phieuNhanXetQA) {
		String query = "INSERT INTO `phieunhanxetqa` (`DanhGiaQA`,`TenTKSV`) "
				+ "VALUES (?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setInt(1, phieuNhanXetQA.getDanhGiaQA());
			preparedStatement.setString(2, phieuNhanXetQA.getTenTKSV());
			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}
	
	public int themCTPhieuNhanXetQA(ChiTietPNXQAEntity ctPhieuNhanXetQA) {
		String query = "INSERT INTO `chitietpnxqa` (`MaPNXQA`,`MaQA`,`NoiDungNhanXetQA`) "
				+ "VALUES (?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setInt(1, ctPhieuNhanXetQA.getMaPNXQA());
			preparedStatement.setInt(2, ctPhieuNhanXetQA.getMaQA());
			preparedStatement.setString(3, ctPhieuNhanXetQA.getNoiDungNhanXetQA());
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}
	
	public int themCTPhieuNhanXetNT(ChiTietPNXNTEntity ctPhieuNhanXetNT) {
		String query = "INSERT INTO `chitietpnxnt` (`MaPNXNT`,`MaNT`,`NoiDungNhanXetNT`) "
				+ "VALUES (?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setInt(1, ctPhieuNhanXetNT.getMaPNXNT());
			preparedStatement.setInt(2, ctPhieuNhanXetNT.getMaNT());
			preparedStatement.setString(3, ctPhieuNhanXetNT.getNoiDungNhanXetNT());
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	public ArrayList<ThongTinNhanXetEntity> loadThongTinNhanXet(int ma, int loaiNhanXet) {
		ArrayList<ThongTinNhanXetEntity> listThongTinNhanXet = new ArrayList<ThongTinNhanXetEntity>();		
		String query = "";
		String loaiNoiDungNhanXet = "";
		
		if(loaiNhanXet == KenhSinhVienContants.PHIEU_NHAN_XET_QUAN_AN) {
			query = "SELECT ct.NoiDungNhanXetQA,pnx.TenTKSV "
					+ "FROM chitietpnxqa ct "
					+ "INNER JOIN quanan AS qa ON ct.MaQA = qa.MaQA "
					+ "INNER JOIN phieunhanxetqa AS pnx "
					+ "ON pnx.MaPNXQA = ct.MaPNXQA "
					+ "WHERE qa.MaQA = " + ma + " and ct.NoiDungNhanXetQA <> '' and pnx.TenTKSV <> ''";
			loaiNoiDungNhanXet = "NoiDungNhanXetQA";
		} else if(loaiNhanXet == KenhSinhVienContants.PHIEU_NHAN_XET_NHA_TRO) {
			query = "SELECT ct.NoiDungNhanXetNT,pnx.TenTKSV "
					+ "FROM chitietpnxnt ct "
					+ "INNER JOIN nhatro AS nt ON ct.MaNT = nt.MaNT "
					+ "INNER JOIN phieunhanxetnt AS pnx ON ct.MaPNXNT = pnx.MaPNXNT "
					+ "WHERE nt.MaNT = " + ma + " and ct.NoiDungNhanXetNT <> '' and pnx.TenTKSV <> ''";
			loaiNoiDungNhanXet = "NoiDungNhanXetNT";
		}
		
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		try {
			conn = dataSource.getConnection();
			preparedStatement = conn.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				ThongTinNhanXetEntity thongTinNhanXetEntity = new ThongTinNhanXetEntity();
				thongTinNhanXetEntity.setNoiDungNhanXet(rs.getString(loaiNoiDungNhanXet));
				thongTinNhanXetEntity.setTenTaiKhoan(rs.getString("TenTKSV"));
				listThongTinNhanXet.add(thongTinNhanXetEntity);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return listThongTinNhanXet;
	}
}
